<?php

/**
 * 数据导入
 * @author Flowf
 */
class ExcelController extends Controller {

    public $layout = 'main';

    /**
     * @var返回过滤数组
     * @author nirsh
     * @version 5.0
     * @demo 
     */
    public function filters() {
        return array(
            'accessControl', // perform access control for CRUD operations
        );
    }

    /**
     * @var返回验证条件 如果不需要验证权限 把$isPriv 设置为allow
     * @author nirsh
     * @version 5.0
     * @demo 
     *    switch($action){
     *      case "login":
     *          $isPriv = 'allow'
     *          break;
     *      default:
     *          $isPriv = $this->checkAccess($controller, $action);
     *      }
     */
    public function accessRules() {
        $controller = Yii::app()->getController()->id;
        $action = Yii::app()->getController()->action->id;
        //登录不需要判断权限
        $isPriv = $this->checkAccess($controller, $action);
        return array(
            array($isPriv,
                'actions' => array($action),
                'users' => array('*'),
            )
        );
    }

    /**
     * 数据导出
     */
    public function actionExportData() {
        $product_model = isset($_POST['product_model']) ? $_POST['product_model'] : "";
        $game = isset($_POST['game']) ? $_POST['game'] : "";

        /**
         * 数据导出
         */
        if (!empty($product_model) && !empty($game)):
            $mimeTypes = array(
                'Excel5' => array(
                    'Content-type' => 'application/vnd.ms-excel',
                    'extension' => 'xls',
                ),
                'Excel2007' => array(
                    'Content-type' => 'application/vnd.ms-excel',
                    'extension' => 'xlsx',
                ),
                'PDF' => array(
                    'Content-type: application/pdf',
                    'extension' => 'pdf',
                ),
                'HTML' => array(
                    'Content-type' => 'text/html',
                    'extension' => 'html',
                ),
                'CSV' => array(
                    'Content-type' => 'application/csv',
                    'extension' => 'csv',
                )
            );
            $attrData = array();
            $rows_data = array();
            $website = $website == 0 ? "" : $website;
            switch ($product_model) {
                case 'GOLD':
                    $model = new ProductGold;
                    $gold_cr = new CDbCriteria;
                    $gold_cr->compare('gold_game', $game);
                    $gold_cr->order = 'gold_num ASC';
                    $rows_data = $model->findAll($gold_cr);
                    break;
                case 'PL':
                    $model = new ProductPowerleveling;
                    $pl_cr = new CDbCriteria;
                    $pl_cr->compare('pl_game', $game);
                    $pl_cr->order = 'pl_level ASC';
                    $rows_data = $model->findAll($pl_cr);
                    break;
                case 'SKILL':
                    $model = new ProductSkill;
                    $sk_cr = new CDbCriteria;
                    $sk_cr->compare('game_id', $game);
                    $sk_cr->order = 'skill_name ASC';
                    $rows_data = $model->findAll($sk_cr);
                    break;
                case 'SKILL_LEVEL':
                    $skill_id = (int) $_POST['skill_id'];
                    $model = new ProductSkillLevel;
                    $sk_cr = new CDbCriteria;
                    $sk_cr->compare('skill_id', $skill_id);
                    $sk_cr->order = 'skill_level ASC';
                    $rows_data = $model->findAll($sk_cr);
                    break;
                case 'GAME_SERVER':
                    $model = new ProductCategory;
                    $g_cr = new CDbCriteria;
                    $g_cr->compare('parentid', $game);
                    $g_cr->order = 'title ASC';
                    $rows_data = $model->findAll($g_cr);
                    break;
                default :
                    $game_info = ProductCategory::model()->findByPk($game);

                    $model = new ProductBase;
//                    $default_cr = new CDbCriteria;
//                    $default_cr->compare('p_game', $game);
//                    $default_cr->compare('p_type', $product_model);
//                    $default_cr->order = 'p_name ASC';
//                    $rows_data = $model->findAll($default_cr);

                    $attrModel = new ProductAttribute;
                    $cr = new CDbCriteria;
                    $cr->compare('game_id', $game_info->parentid);
                    $cr->compare('product_type', $product_model);
                    $cr->compare('status', 1);
                    $cr->order = 'order_sort ASC';
                    $cat_str = '';
                    $concat_str = '';
                    foreach ($attrModel->findAll($cr) as $value) {
                        $attrData[$value->id] = "{$value->name}_{$value->id}";
                        $cat_str .= "CASE attribute_id WHEN {$value->id} THEN attribute_value END AS {$value->name}_{$value->id},";
                        $concat_str .= "GROUP_CONCAT({$value->name}_{$value->id}) as {$value->name}_{$value->id},";
                    }
                    $cat_str = trim($cat_str, ',');
                    $concat_str = trim($concat_str, ',');
                    if(!empty($cat_str)){
                        $sql = "
                        SELECT 
                        p_game, p_name, p_price, p_quntity, p_rebate, p_type, p_pro, p_note, p_img,
                        $concat_str
                        FROM (
                        SELECT
                        *,
                        $cat_str
                        FROM(
                        SELECT
                        p.*,
                        pav.attribute_id,
                        pav.attribute_value
                        FROM
                        product_base AS p
                        LEFT JOIN
                        product_attribute_value AS pav
                        ON
                        pav.product_id=p.id
                        WHERE
                        p.p_game=$game AND p.p_type='$product_model'
                        ) AS pv
                        ) AS ppv
                        GROUP BY id
                        ";
                    }  else {
                        $sql = "SELECT * FROM product_base WHERE p_game=$game AND p_type='$product_model'";
                    }
//echo $sql;
//exit;
                    $rows_data = Yii::app()->db->createCommand($sql)->queryAll();

                    break;
            }
            $iniData = $model->attributeLabels();
            if (!empty($attrData))
                $iniData += $attrData;

//导入接口
            spl_autoload_unregister(array('YiiBase', 'autoload'));
            Yii::import('application.extensions.phpexcel.Classes.PHPExcel', true);
            $objPHPExcel = new PHPExcel();
            spl_autoload_register(array('YiiBase', 'autoload'));
// Creating a workbook
            $product_name = $product_model != 'GAME_SERVER' ? Yii::app()->params->model_array[$product_model] : '服务区';
            $game_name = ProductCategory::model()->getGameNameById((int) $game);
            $title = $game_name . '-' . $product_name . "-数据-" . date('Y-m-d');
            $description = "数据中心数据";
            $objPHPExcel->getProperties()->setCreator('Usfine');
            $objPHPExcel->getProperties()->setTitle($title);
            $objPHPExcel->getProperties()->setSubject('数据中心');
            $objPHPExcel->getProperties()->setDescription($description);
            $objPHPExcel->getProperties()->setCategory('');

//获取栏位标题
            $i = 0;
            foreach ($iniData as $key => $value) {
                if ($key == 'site_key' || $key == 'id' || $key == 'p_type' || $key=='update_time' || $key=='p_time') {//分站，自动编号，产品类型不入表格
                    continue;
                }
                $fName = $value;

                if ($i <= 25) {
                    $col = chr(ord('A') + $i);
                } else {
                    $col = chr(ord('A')) . chr(ord('A') + $i - 26);
                }
                $colTitle = $col . "1";
                //echo $colTitle . "<br>";
                $head = $fName;
                $objPHPExcel->getActiveSheet()->setCellValue($colTitle, $head);
                $colValue = $col . "2";
                $objPHPExcel->getActiveSheet()->getColumnDimension($colValue)->setAutoSize(false);
                ++$i;
            }
            $n = 2;
            foreach ($rows_data as $row) {
                $i = 0;

                foreach ($row as $key => $value) {
                    if ($key == 'site_key' || $key == 'id' || $key == 'p_type' || $key=='update_time' || $key=='p_time') {//分站，自动编号，产品类型不入表格
                        continue;
                    }
                    
                    if(in_array($key, $attrData)){
                        $at_value = unserialize($value);
                        $value = "";
                        
                        foreach ($at_value['num'] as $atkey => $attv){
                            $value .= $at_value['num'][$atkey]."|".$at_value[unit][$atkey]."|".$at_value[discount][$atkey]."\r\n";
                        }
                        
                    }
                    $fName = $value;

                    if ($i <= 25) {
                        $col = chr(ord('A') + $i);
                    } else {
                        $col = chr(ord('A')) . chr(ord('A') + $i - 26);
                    }
                    $colTitle = $col . $n;
                    //echo $colTitle . "<br>";
                    $head = $fName;
                    $objPHPExcel->getActiveSheet()->setCellValue($colTitle, $head);
                    //$colValue = $col . "2";
                    //$objPHPExcel->getActiveSheet()->getColumnDimension($colValue)->setAutoSize(false);
                    ++$i;
                }
                $n++;
            }


//输出表格
            $exportType = "Excel2007";
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $exportType);
            ob_end_clean();
            header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
            header('Pragma: public');
            header('Content-type: ' . $mimeTypes[$exportType]['Content-type']);
            header('Content-Disposition: attachment; filename="' . $title . '.' . $mimeTypes[$exportType]['extension'] . '"');
            header('Cache-Control: max-age=0');
            $objWriter->save('php://output');
            Yii::app()->end();
        endif; //数据导出结束

        $this->render('exportData', array(
            'product_model' => $product_model,
            'website' => $website,
            'game' => $game
        ));
    }

    /**
     * 导出数据表格
     */
    public function actionExportExcel() {
        $product_model = $_GET['product_model'];
        $game = $_GET['game'];

        $this->render('exportExcel', array(
            'product_model' => $product_model,
            'game' => $game,
        ));
    }

    /**
     * 数据导入
     */
    public function actionImportData() {
        set_time_limit(3600);//防超时
        $rMsg = array('msg' => "导入数据存在问题！", 'a_count' => 0, 'i_count' => 0);
        $eModel = new ExcelMono;
        if ($_FILES['leadExcel']['name']) {
            $filename = $_FILES['leadExcel']['name'];
            $tmp_name = $_FILES['leadExcel']['tmp_name'];

            //产品类型
            $eModel->type = $_POST['product_model'];

            //游戏
            $eModel->game = (int)$_POST['game'];
            $game_info = ProductCategory::model()->findByPk((int)$_POST['game']);
            $eModel->game_parent = $game_info->parentid;
            //技能ID
            $eModel->skillId = isset($_POST['skill_id']) ? (int) $_POST['skill_id'] : 0;

            $rMsg = $eModel->uploadFile($filename, $tmp_name);
        }

        $this->render('importData', array(
            'type' => $_POST['product_model'],
            'game' => $_POST['game'],
            'rMsg' => $rMsg,
            'eModel' => $eModel,
        ));
    }

    /**
     * 获取技能
     */
    public function actionGetSkill() {
        $game_id = (int) $_POST['game_id'];
        if ($game_id) {
            $model = new ProductSkill;
            $select = "<option value=''>选择技能</option>";
            foreach ($model->findAll("game_id=$game_id") as $skill) {
                $select .= "<option value='{$skill->id}'>{$skill->skill_name}</option>";
            }
            echo "<select name='skill_id' id='skill_id' class='form-input' rel-required='true' rel-message='请选择技能'>{$select}</select>";
            exit;
        }
    }

    /**
     * AJAX更新重复数据
     */
    public function actionUpdateAjax() {
        if (Yii::app()->request->isAjaxRequest) {
            $id = (int) $_POST['id'];
            $product_model = $_POST['productModel'];
            $game_id = (int) $_POST['game'];
            switch ($product_model) {
                case 'GOLD'://更新金币产品
                    $model = ProductGold::model()->findByPk($id);
                    $model->attributes = array(
                        'gold_game' => $game_id,
                        'gold_name' => $_POST['gold_name'],
                        'gold_num' => $_POST['gold_num'],
                        'gold_unit' => $_POST['gold_unit'],
                        'gold_discount' => $_POST['gold_discount'],
                    );
                    echo $model->update();
                    break;
                case 'PL'://更新代练产品
                    $model = ProductPowerleveling::model()->findByPk($id);
                    $model->attributes = $_POST;
                    echo $model->update();
                    break;
                case 'SKILL'://技能
                    $model = ProductSkill::model()->findByPk($id);
                    $model->attributes = $_POST;
                    echo $model->update();
                    break;
                case 'SKILL_LEVEL'://技能等级
                    $model = ProductSkillLevel::model()->findByPk($id);
                    $model->attributes = $_POST;
                    echo $model->update();
                    break;
                case 'GAME_SERVER'://服务区
                    $model = ProductCategory::model()->findByPk($id);
                    $model->attributes = $_POST;
                    echo $model->update();
                    break;
                default:
                    $model = ProductBase::model()->findByPk($id);
                    $model->attributes = $_POST;
                    $model->update();
                    $game_info = ProductCategory::model()->findByPk($game_id);
                    $attrs = ProductAttribute::model()->findAllByAttributes(array(
                        'product_type' => $product_model,
                        'game_id' => $game_info->parentid,
                            ));
                    $attrValModel = new ProductAttributeValue;
                    foreach ($attrs as $attr) {
                        $attrValData = $attrValModel->findByAttributes(array(
                            'product_id' => $model->id,
                            'attribute_id' => $attr->id,
                                ));
                        if ($attrValData) {
                             $value = '';
                            switch ($attr->input_type) {
                                case 'NUMS':
                                    $rows_array = explode("\n", $_POST[$attr->id]);
                                    $nums_array = array('num'=> array(), 'unit' => array(), 'discount' => array());
                                    foreach ($rows_array as $row_str) {
                                        if ($row_str) {
                                            $s_array = explode('|', $row_str);
                                            $nums_array['num'][] = $s_array[0];
                                            $nums_array['unit'][] = $s_array[1];
                                            $nums_array['discount'][] = $s_array[2];
                                        }
                                    }
                                    $value = serialize($nums_array);
                                    break;
                                default:
                                    $value = $_POST[$attr->id];
                                    break;
                            }
                            $attrValData->attributes = array(
                                'attribute_value' => $value,
                            );
                            $attrValData->update();
                        }
                    }
                    echo 1;
                    break;
            }
        }
    }

}